package assem;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.regex.Pattern;
import java.util.Vector;

import assem.enums.LogLevel;

// WARNING: Does not work for nested queries (because it uses one Statement for all queries). 
// To do a query within the read loop of a previous
// query, use a second DBConn object.

public class DBConn 
{
	static Pattern mpQUOT = null;
	final int maxTries = 10;
	public String mBadStrMsg = "Quotes not permitted";
	public String mUser;
	public String mPass;
	public String mDBName;
	public String mHost;
    public String jdbcStr = "";

	Connection mConn = null;
	Statement mStmt = null;

    public DBConn(String host, String dbname,  String user, String pass) throws Exception
    {
            mHost = host;
            mDBName = dbname;
            mUser = user;
            mPass = pass;
            jdbcStr = "jdbc:mysql://" + mHost + "/"        + mDBName;
            renew();
    }
    // The next is for connection w/o specific database, for doing e.g. "show databases".
    // User needs sufficient permissions to do this. 
    public DBConn(String host, String user, String pass) throws Exception
    {
            mHost = host;
            mUser = user;
            mPass = pass;
            jdbcStr = "jdbc:mysql://" + mHost ;
            renew();
    }	
	public Connection getDBconn() { return mConn;}
	
	public void renew() throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");
		mStmt = null;
		if (mConn != null && !mConn.isClosed()) mConn.close();
		for (int i = 0; i <= maxTries; i++)
		{
			try
			{
				mConn = DriverManager.getConnection(jdbcStr, mUser,mPass);
				break;
			} 
			catch (SQLException e)
			{
				if (i == maxTries)
				{
					Log.exception(e);
					//e.printStackTrace(); CAS 2/6/15
					Log.die("Unable to connect to " 	+ jdbcStr
								+ "\nError message: "
								+ e.getMessage());
				}
			}
			Thread.sleep(100);
		}
	}
    public static boolean connectionValid(String host, String dbName, String user, String pass) {
        try {
                Class.forName("com.mysql.jdbc.Driver");
                Connection conn = DriverManager.getConnection("jdbc:mysql://" + host + "/"      + dbName, user, pass);

                SQLWarning warn = conn.getWarnings();
                while (warn != null) {
                        System.out.println("SQLState: " + warn.getSQLState());
                        System.out.println("Message:  " + warn.getMessage());
                        System.out.println("Vendor:   " + warn.getErrorCode());
                        System.out.println("");
                        warn = warn.getNextWarning();
                }
                return true;
        }
        catch(Exception e) {
                System.err.println("Error: Unable to load database");
                return false;
        }
    }

    public String getDbiStr()
    {
    		return "dbi:mysql:" + mDBName + ":" + mHost;
    }
    public static boolean checkMysqlServer(String host, String user, String pass)
    {
            // First try to find the driver
            boolean ret = false;
            try
            {
                    Class.forName("com.mysql.jdbc.Driver");
            }
            catch(Exception e)
            {
                    System.err.println("Unable to find MySQL driver");
            }
            // First try just the server
            String dbstr = "jdbc:mysql://" + host;

            try
            {
                    Connection con = DriverManager.getConnection(dbstr, user, pass);
                    con.close();
                    ret = true;
            }
            catch (Exception e)
            {	// CAS 5/23/15 removed user and pass as its shown on applet Java console
                    System.err.println("Cannot connect to database with host=" + host);
            }
            return ret;
    }

	public static boolean checkMysqlDB(String msg, String host, String db, String user, String pass) 
	{
		String dbstr = "jdbc:mysql://" + host + "/" + db;
		Connection con = null; 

		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(dbstr, user, pass);
			con.close();
			return true;
		} 
		catch (Exception e)
		{
			//Log.msg("Database " + db + " not found on host " + host,LogLevel.Detail);	
		}
		return false;
	}	
	public void deleteDB()
	{
		try
		{
			executeUpdate("drop database " + mDBName);
			close();
		}
		catch (Exception e)
		{
			Log.die("Database " + mDBName + " could not be deleted");	
			e.printStackTrace();
		}	
	}
	public boolean isEmpty() 
	{
		try
		{
			ResultSet rs = executeQuery("show tables like 'clone'");
			if (!rs.first()) 
			{
				return true;
			}
			rs = executeQuery("select count(*) from clone");
			rs.first();
			int nClones = rs.getInt(1);
			rs.close();
			return nClones == 0;			
		} 
		catch (Exception e)
		{
			// Should never get here as DB existence is already checked
			Log.die("Database  not found on host ");
			e.printStackTrace();
		}
		return false;
	}	
	public static boolean oldDBisEmpty(String host, String db, String user, String pass) 
	{
		String dbstr = "jdbc:mysql://" + host + "/" + db;
		Connection con = null; 

		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(dbstr, user, pass);
			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery("show tables like 'clone'");
			if (!rs.first()) 
			{
				con.close();
				return true;
			}
			rs = s.executeQuery("select count(*) from clone");
			rs.first();
			int nClones = rs.getInt(1);
			rs.close();
			con.close();
			return nClones == 0;			
		} 
		catch (Exception e)
		{
			// Should never get here as DB existence is already checked
			Log.die("Database " + db + " not found on host " + host);	
		}
		return false;
	}	
	public static boolean deleteDB(String host, String db, String user, String pass) 
	{
		String dbstr = "jdbc:mysql://" + host + "/mysql";
		Connection con = null; 

		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(dbstr, user, pass);
			Statement s = con.createStatement();
			s.executeUpdate("drop database " + db);
			Log.indentMsg("Deleted database " + db,LogLevel.Basic);
			return true;
		} 
		catch (Exception e)
		{
			Log.die("Database " + db + " could not be deleted");	
			e.printStackTrace();
		}
		return false;
	}	
	public static void createMysqlDB(String host, String db, String user, String pass) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");

		String dbstr = "jdbc:mysql://" + host;
		Connection con = null; 

		try
		{
			con = DriverManager.getConnection(dbstr, user, pass);
			Statement st = con.createStatement();
			st.execute("create database " + db);
			con.close();
		} 
		catch (Exception e)
		{
			if (user.equals("") || pass.equals("")) 
				System.err.println("Warning: User and password may not be defined in HOSTS.cfg");
			Log.die("Database " + db + " could not be created on host " + host);	
		}
	}	
	public boolean hasTables() throws Exception
	{
		ResultSet rs = executeQuery("show tables");
		return rs.first();
	}
	public boolean hasTable(String name) throws Exception
	{
		ResultSet rs = executeQuery("show tables like '" + name + "'");
		return rs.first();
	}
	
	public boolean tableHasColumn(String table, String column) throws Exception
	{
		boolean ret = false;
		ResultSet rs = executeQuery("show columns from " + table + " where field='" + column + "'");
		if (rs.first()) ret = true;
		rs.close();
		return ret;
	}
	public void checkAddColumn(String table, String col, String type, String aft) throws Exception
	{
		try {
			if (!tableHasColumn(table,col))
			{
				String cmd = "alter table " + table + " add " + col + " " + type ;
				if (!aft.trim().equals(""))
				{
					cmd += " after " + aft;
				}
				executeUpdate(cmd);
			}
		}
		catch(Exception e)
		{
			System.out.println("Warning: error adding " + table + "." + col);
			e.printStackTrace();
		}
		
	}
	public void checkDropColumn(String table, String col) throws Exception
	{
		if (tableHasColumn(table,col))
		{
			String cmd = "alter table " + table + " drop " + col;
			executeUpdate(cmd);
		}
	}
	// Change column to new definition, if it doesn't already match.
	// Note, the definition must be given exactly as seen in the "show table" listing,
	// e.g. mediumint(8) and not just mediumint.
	// If defs don't match, it will re-change the column, wasting time. 
	public void checkChangeColumn(String table, String col, String type) throws Exception
	{
		if (tableHasColumn(table,col))
		{
			String curDesc = getColDesc(table,col);
			if (!curDesc.equalsIgnoreCase(type))
			{
				String cmd = "alter table " + table + " change " + col + " " + col + " " + type ;
				executeUpdate(cmd);
			}
		}
		else
		{
			System.err.println("Warning: tried to change column " + table + "." + col + ", which does not exist");
		}
	}
	public String getColDesc(String tbl, String col)
	{
		String ret = "";
		try
		{
			ResultSet rs = executeQuery("describe " + tbl);
			while (rs.next())
			{
				String fld = rs.getString("Field");
				String desc = rs.getString("Type");
				if (fld.equalsIgnoreCase(col))
				{
					ret = desc;
					break;
				}
			}
		}
		catch(Exception e)
		{
			System.out.println("Warning: error checking column description for " + tbl + "." + col);
		}
		return ret;
	}
	public Statement getStatement() throws Exception
	{
		if (mStmt == null)
		{
			mStmt = mConn.createStatement();
			mStmt.setQueryTimeout(10000);
		}
		return mStmt;
	}
	public ResultSet executeQuery(String sql) throws Exception
	{
		//closeLast();
		if (mConn == null || mConn.isClosed()) renew();
		Statement stmt = getStatement();
		ResultSet rs = null;
		try
		{
			rs = stmt.executeQuery(sql);
		}
		catch (Exception e)
		{
			// retry, with new statement
			Log.msg("Query failed, retrying:" + sql, LogLevel.Detail);
			mStmt.close();
			mStmt = null;
			renew();
			stmt = getStatement();
			rs = stmt.executeQuery(sql);
		}
		return rs;
	}

	public int executeUpdate(String sql) throws Exception
	{
		//closeLast();
		if (mConn == null || mConn.isClosed()) renew();
		Statement stmt = getStatement();
		int ret = 0;
		try
		{
			ret = stmt.executeUpdate(sql);
		}
		catch (Exception e)
		{
			// retry, with new statement
			Log.msg("Query failed, retrying:" + sql, LogLevel.Detail);
			mStmt.close();
			mStmt = null;
			renew();
			stmt = getStatement();
			ret = stmt.executeUpdate(sql);
		}
		
		//stmt.close();
		return ret;
	}
	public Statement createStatement() throws Exception
	{
		return mConn.createStatement();
	}

	// NOT THREAD SAFE unless each thread is using its own DB connection.
	// Each thread should call Assem.getDBConnection() to get one. 
	
	public Integer lastID() throws Exception
	{
		String st = "select last_insert_id() as id";
		ResultSet rs = executeQuery(st);
		int i = 0;
		if (rs.next())
		{
			i = rs.getInt("id");
		}
		rs.close();
		return i;
	}

	public void openTransaction() throws Exception
	{
		executeQuery("BEGIN");
	}
	public void closeTransaction() throws Exception
	{
		executeQuery("COMMIT");
	}	
	public void rollbackTransaction() throws Exception
	{
		executeQuery("ROLLBACK");
	}	

	public  PreparedStatement prepareStatement(String st) throws SQLException
	{
		return mConn.prepareStatement(st);
	}
	public void close() throws Exception
	{
		mConn.close();
		mConn = null;
	}
	public void foreignKeysOff() throws Exception
	{
		executeQuery("set foreign_key_checks=0");	
	}
	public void foreignKeysOn() throws Exception
	{
		executeQuery("set foreign_key_checks=1");	
	}	
	public void writeLockTable(String table) throws Exception
	{
		executeQuery("lock tables " + table + " write");	
	}
	public void lockAllTables() throws Exception
	{
		// get the tables to a vector first since we can't
		// do nested queries
		Vector<String> tbls = new Vector<String>();
		ResultSet rs = executeQuery("show tables");	
		while (rs.next())
		{
			String table = rs.getString(1);
			tbls.add(table);
		}
		
		for (String table : tbls)
		{
			executeQuery("lock tables " + table + " write");
		}
	}
	public void unlockTables() throws Exception
	{
		executeQuery("unlock tables");	
	}
	public void checkInnodbBufPool() throws Exception
	{
		ResultSet rs = executeQuery("show variables like 'innodb_buffer_pool_size'");
		if (rs.first())
		{
			long size = rs.getLong(2);
			if (size < 100000000L)
			{
				Log.msg("\nYour database variable 'innodb_buffer_pool_size' is set to a small number (" + size + ")", LogLevel.Basic);
				Log.msg("It is strongly recommended to raise this and related settings (see documentation).", LogLevel.Basic);
				if (!Utils.yesNo("The assembly may be slow with these settings. Continue"))
				{
					System.exit(0);	
				}
			}
		}
		else
		{
			Log.msg("Attempted to check database variable 'innodb_buffer_pool_size' but was not successful.\nEnsure that this setting, and others, are large enough for your assembly (see documentation).", LogLevel.Basic); 	
		}
	}
	public boolean hasInnodb() throws Exception
	{
		boolean has = false;
		ResultSet rs = executeQuery("show engines");
		while (rs.next())
		{
			String engine = rs.getString(1);
			String status = rs.getString(2);
			if (engine.equalsIgnoreCase("innodb") && (status.equalsIgnoreCase("yes")
					|| status.equalsIgnoreCase("default")) )
			{
				has = true;
				break;
			}
		}
		return has;
	}
	public boolean tableExists(String name) throws Exception
	{
		ResultSet rs = executeQuery("show tables like '" + name + "'");
		return rs.first();
	}
    public static void deleteMysqlDB(String host, String db, String user, String pass) throws Exception
    {
            Class.forName("com.mysql.jdbc.Driver");
            String dbstr = "jdbc:mysql://" + host;
            Connection con = null;

            try
            {
                    con = DriverManager.getConnection(dbstr, user, pass);
                    Statement st = con.createStatement();
                    st.execute("drop database " + db);
                    con.close();
            }
            catch (Exception e)
            {
                    System.err.println( "Cannot delete mySQL database " + db );
                    e.printStackTrace();
            }
    }
    public int executeCount(String sql) throws Exception
    {
            try {
                    ResultSet rs = executeQuery(sql);
                    rs.next();
                    int n = rs.getInt(1);
                    rs.close();
                    return n;
            }
            catch (Exception e) {
                    System.err.println( "Error executing count");
                    return 0;
            }
    }
    public ResultSet executeQueryNoCatch(String sql) throws Exception
    {
            //closeLast();
            if (mConn == null || mConn.isClosed()) renew();
            Statement stmt = getStatement();
            ResultSet rs = null;
            rs = stmt.executeQuery(sql);
            return rs;
    }
    public DBConn createDBAndNewConnection(String dbName) throws Exception
    {
            Connection con = null;

            try
            {
                    executeUpdate("create database " + dbName);
                    return  new DBConn(mHost, dbName, mUser, mPass);

            }
            catch (Exception e)
            {
                    System.err.println( "Cannot create MySQL databse " + dbName);
                    System.exit(0);
            }
            return null;
    }

}
